﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WeFrame.AutoCode
{
    class DALHelper
    {

        public string GenerateDALText(TableEntity tableEntity, List<ColumnEntity> columnList, ConfigParameter configParameter)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("using System;");
            sb.AppendLine("using System.Collections.Generic;");
            sb.AppendLine("using System.Text;");
            sb.AppendLine("using System.Data;");
            sb.AppendLine("using MySql.Data.MySqlClient;");
            sb.AppendLine("using WeFrame.Core.Common;");
            sb.AppendLine("using WeFrame.Core.DBUtility;");
            sb.AppendLine("using WeFrame.Core.Model;");
            sb.AppendLine("using "+configParameter.NameSpaceTag+".Model;");
            sb.AppendLine("namespace "+configParameter.NameSpaceTag+"."+configParameter.DalTag);
            sb.AppendLine("{");


            sb.AppendLine("    /// <summary>");
            sb.AppendLine("    /// 数据访问类:"+tableEntity.Description);
            sb.AppendLine("    /// </summary>");
            sb.AppendLine("    ///");


            sb.AppendLine("    public class " + tableEntity.TableName +configParameter.DalTag );
            sb.AppendLine("    {" );


            sb.AppendLine("        public " + tableEntity.TableName + configParameter.DalTag+"()" );
            sb.AppendLine("        {");
            sb.AppendLine("        }");
            sb.AppendLine("");
            sb.AppendLine("        #region 基本方法=============================");
            sb.AppendLine("");

            sb.AppendLine("        /// <summary>");
            sb.AppendLine("        /// 是否存在该记录");
            sb.AppendLine("        /// </summary>");
            sb.AppendLine("        public bool Exists(int id)");
            sb.AppendLine("        {");
            sb.AppendLine("            StringBuilder strSql = new StringBuilder();");
            sb.AppendLine("            strSql.Append(\"select count(1) from  "+tableEntity.InitialTableName+"\");");
            sb.AppendLine("            strSql.Append(\" where id=@id \");");
            sb.AppendLine("            MySqlParameter[] parameters = {");
            sb.AppendLine("                 new MySqlParameter(\"@id\", MySqlDbType.Int32,4)};");
            sb.AppendLine("            parameters[0].Value = id;");
            sb.AppendLine("            return DbHelperSQL.Exists(strSql.ToString(), parameters);");
            sb.AppendLine("        }");
            sb.AppendLine("");
            sb.AppendLine("        /// <summary>");
            sb.AppendLine("        /// [增加]一条数据");
            sb.AppendLine("        /// </summary>");
            sb.AppendLine("        /// <param Name=\"model\">数据对象</param>");
            sb.AppendLine("        /// <returns>插入后数据的流水号 0 表示插入失败</returns>");
            sb.AppendLine("        public  int Add("+tableEntity.TableName+"Entity model)");
            sb.AppendLine("        {");
            sb.AppendLine("             string sql = @\"INSERT INTO "+tableEntity.InitialTableName);
            sb.AppendLine("                    (");
            sb.AppendLine("                    "+BuildInsertField(columnList));
            sb.AppendLine("                    )");
            sb.AppendLine("                    VALUES");
            sb.AppendLine("                    (");
            sb.AppendLine("                    "+BuildInsertParam(columnList));
            sb.AppendLine("                    )\";select @@IDENTITY;");
            sb.AppendLine("             "+BuildMySqlParameter(columnList,false));
            sb.AppendLine("            object value = "+configParameter.DbHelperTag+".GetSingle(sql, parameters);");
            sb.AppendLine("            if (value != null)");
            sb.AppendLine("            {");
            sb.AppendLine("                return Int32.Parse(value.ToString());");
            sb.AppendLine("            }");
            sb.AppendLine("            return 0;");
            sb.AppendLine("        }");
            sb.AppendLine("");
            sb.AppendLine("");
            sb.AppendLine("        /// <summary>");
            sb.AppendLine("        /// [查询]根据id获得对象");
            sb.AppendLine("        /// </summary>");
            sb.AppendLine("        /// <param Name=\"id\">编号</param>");
            sb.AppendLine("        /// <returns>实体对象</returns>");
            sb.AppendLine("        public  " + tableEntity.TableName + "Entity GetModel(int id)");
            sb.AppendLine("        {");
            sb.AppendLine("             string sql = @\"SELECT * FROM " + tableEntity.InitialTableName + " WHERE Id=@Id\";");
            sb.AppendLine("             MySqlParameter[] parameters = {");
            sb.AppendLine("                                new MySqlParameter(\"@Id\",id)");
            sb.AppendLine("                            };");
            sb.AppendLine("             DataSet ds =" + configParameter.DbHelperTag + ".Query(sql, parameters);");
            sb.AppendLine("             if (ds.Tables[0].Rows.Count > 0) ");
            sb.AppendLine("             {");
            sb.AppendLine("                 return DataRowToEntity(ds.Tables[0].Rows[0]);");
            sb.AppendLine("             }");
            sb.AppendLine("             return null;");
            sb.AppendLine("        }");
            sb.AppendLine("");
            sb.AppendLine("");
            sb.AppendLine("");
            sb.AppendLine("");

            sb.AppendLine("        /// <summary>");
            sb.AppendLine("        /// 更新一个实体");
            sb.AppendLine("        /// </summary>");
            sb.AppendLine("        /// <param name=\"model\"></param>");
            sb.AppendLine("        /// <returns></returns>");
            sb.AppendLine("        public bool Update(" + tableEntity.TableName + "Entity model)");
            sb.AppendLine("        {");
            sb.AppendLine("             StringBuilder strSql = new StringBuilder();");
            sb.AppendLine("             strSql.Append(\"update " + tableEntity.InitialTableName + " set \");");
            sb.AppendLine("             strSql.Append(\""+BuildUpdateParam(columnList)+"\");");
            sb.AppendLine("             strSql.Append(\" where Id=@id\");");
            sb.AppendLine("             " + BuildMySqlParameter(columnList,true));
            sb.AppendLine("             int rows = " + configParameter.DbHelperTag + ".ExecuteSql(strSql.ToString(), parameters);");
            sb.AppendLine("             if (rows > 0)");
            sb.AppendLine("             {");
            sb.AppendLine("                 return true;");
            sb.AppendLine("             }");
            sb.AppendLine("             else");
            sb.AppendLine("             {");
            sb.AppendLine("                 return false;");
            sb.AppendLine("             }");
            sb.AppendLine("        }");



            sb.AppendLine("        /// <summary>");
            sb.AppendLine("        /// 删除一条");
            sb.AppendLine("        /// </summary>");
            sb.AppendLine("        /// <param name=\"id\"></param>");
            sb.AppendLine("        /// <returns></returns>");
            sb.AppendLine("        public bool Delete(int id)");
            sb.AppendLine("        {");
            sb.AppendLine("            StringBuilder strSql = new StringBuilder();");
            sb.AppendLine("            strSql.Append(\"delete from " + tableEntity.InitialTableName + " \");");
            sb.AppendLine("            strSql.Append(\" where Id=@id\");");
            sb.AppendLine("            MySqlParameter[] parameters = {");
            sb.AppendLine("                 new MySqlParameter(\"@id\", id)};");
            sb.AppendLine("            int rows = " + configParameter.DbHelperTag + ".ExecuteSql(strSql.ToString(), parameters);");
            sb.AppendLine("            if (rows > 0)");
            sb.AppendLine("            {");
            sb.AppendLine("                return true;");
            sb.AppendLine("            }");
            sb.AppendLine("            else");
            sb.AppendLine("            {");
            sb.AppendLine("                return false;");
            sb.AppendLine("            }");
            sb.AppendLine("        }");
            sb.AppendLine("        #endregion 基本方法=============================");

            sb.AppendLine("");
            sb.AppendLine("");
            sb.AppendLine("         /// <summary>");
            sb.AppendLine("         /// [查询]带分页多条件、多排序查询");
            sb.AppendLine("         /// </summary>");
            sb.AppendLine("         /// <param name=\"queryParamList\">查询参数集合</param>");
            sb.AppendLine("         /// <param name=\"queryOrderList\">排序参数集合</param>");
            sb.AppendLine("         /// <param name=\"queryPage\">分页</param>");
            sb.AppendLine("         /// <returns>数据集合</returns>");
            sb.AppendLine("         public  DataSet QueryDataSet(List<QueryParam> queryParamList,List<QueryOrder> queryOrderList, QueryPage queryPage)");
            sb.AppendLine("         {");
            sb.AppendLine("             string where = \" WHERE 1=1\";");
            sb.AppendLine("             string count =\"\";");
            sb.AppendLine("             string sql = \"\";");
            sb.AppendLine("             MySqlParameter[] parameters;");
            sb.AppendLine("             //构造查询参数");
            sb.AppendLine("             parameters = Utils.BuildSqlParameters(queryParamList);");
            sb.AppendLine("             //构造查询条件");
            sb.AppendLine("             where = Utils.BuildWhere(queryParamList);");
            sb.AppendLine("             //查询对象总数");
            sb.AppendLine("             count = \"SELECT COUNT(*) FROM " + tableEntity.InitialTableName + " \"+ where;");
            sb.AppendLine("             queryPage.ObjectCount = Convert.ToInt32(DbHelperSQL.GetSingle(count, parameters));");
            sb.AppendLine("             //获得查询对象集合");
            sb.AppendLine("             sql = Utils.BuildQueryPageSql(\""+tableEntity.InitialTableName+"\",queryParamList, queryOrderList, queryPage);");
            sb.AppendLine("             DataSet dataSet = " + configParameter.DbHelperTag + ".Query(sql, parameters);");
            sb.AppendLine("             return dataSet;");
            sb.AppendLine("         }");


            sb.AppendLine("");
            sb.AppendLine("");
            sb.AppendLine("         /// <summary>");
            sb.AppendLine("         /// [查询]带分页多条件、多排序查询");
            sb.AppendLine("         /// </summary>");
            sb.AppendLine("         /// <param name=\"queryParamList\">查询参数集合</param>");
            sb.AppendLine("         /// <param name=\"queryOrderList\">排序参数集合</param>");
            sb.AppendLine("         /// <param name=\"queryPage\">分页</param>");
            sb.AppendLine("         /// <returns>对象集合</returns>");
            sb.AppendLine("         public  List<" + tableEntity.TableName + "Entity> QueryList(List<QueryParam> queryParamList,List<QueryOrder> queryOrderList, QueryPage queryPage)");
            sb.AppendLine("         {");
            sb.AppendLine("             string where = \" WHERE 1=1\";");
            sb.AppendLine("             string count =\"\";");
            sb.AppendLine("             string sql = \"\";");
            sb.AppendLine("             MySqlParameter[] parameters;");
            sb.AppendLine("             //构造查询参数");
            sb.AppendLine("             parameters = Utils.BuildSqlParameters(queryParamList);");
            sb.AppendLine("             //构造查询条件");
            sb.AppendLine("             where = Utils.BuildWhere(queryParamList);");
            sb.AppendLine("             //查询对象总数");
            sb.AppendLine("             count = \"SELECT COUNT(*) FROM " + tableEntity.InitialTableName + " \"+ where;");
            sb.AppendLine("             queryPage.ObjectCount = Convert.ToInt32(DbHelperSQL.GetSingle(count, parameters));");
            sb.AppendLine("             //获得查询对象集合");
            sb.AppendLine("             sql = Utils.BuildQueryPageSql(\"" + tableEntity.InitialTableName + "\",queryParamList, queryOrderList, queryPage);");
            sb.AppendLine("             DataSet dataSet = " + configParameter.DbHelperTag + ".Query(sql, parameters);");
            sb.AppendLine("             if (dataSet.Tables[0].Rows.Count > 0) ");
            sb.AppendLine("             {");
            sb.AppendLine("                 return RowsToList(dataSet.Tables[0].Rows);");
            sb.AppendLine("             }");
            sb.AppendLine("             return null;");
            sb.AppendLine("         }");
            sb.AppendLine("");
            sb.AppendLine("");
            sb.AppendLine("         /// <summary>");
            sb.AppendLine("         /// 将一行数据转换为对象");
            sb.AppendLine("         /// </summary>");
            sb.AppendLine("         /// <param name=\"dataRow\">一行数据</param>");
            sb.AppendLine("         /// <returns>一个数据对象</returns>");
            sb.AppendLine("         private  " + tableEntity.TableName + "Entity DataRowToEntity(DataRow dataRow)");
            sb.AppendLine("         {");
            sb.AppendLine("             " + tableEntity.TableName + "Entity entity = new " + tableEntity.TableName + "Entity();");
            sb.AppendLine("             DataRow row = dataRow;");
            sb.AppendLine(              BuildEntityProperty(columnList));
            sb.AppendLine("             return entity;");
            sb.AppendLine("         }");
            sb.AppendLine("");
            sb.AppendLine("");
            sb.AppendLine("         /// <summary>");
            sb.AppendLine("         /// 将多行数据转化为对象集合");
            sb.AppendLine("         /// </summary>");
            sb.AppendLine("         /// <param name=\"dataRows\">多行数据</param>");
            sb.AppendLine("         /// <returns>对象集合</returns>");
            sb.AppendLine("         private  List<" + tableEntity.TableName + "Entity> RowsToList(DataRowCollection dataRows) ");
            sb.AppendLine("         {");
            sb.AppendLine("             int count = dataRows.Count;");
            sb.AppendLine("             List<" + tableEntity.TableName + "Entity> list = new List<" + tableEntity.TableName + "Entity>();");
            sb.AppendLine("             for (int i = 0; i < count; i++)");
            sb.AppendLine("             {");
            sb.AppendLine("                 " + tableEntity.TableName + "Entity entity = DataRowToEntity(dataRows[i]);");
            sb.AppendLine("                 list.Add(entity);");
            sb.AppendLine("             }");
            sb.AppendLine("             return list;");
            sb.AppendLine("         }");


            sb.AppendLine("    }");
            sb.AppendLine("}");
            return sb.ToString();

        }

        public static string BuildEntityProperty(List<ColumnEntity> columnList)
        {
            string str = "";
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < columnList.Count; i++)
            {
                string name = columnList[i].Name;
                string type = columnList[i].Type;
                sb.AppendLine("             if((row[\""+name+"\"]!=null)&&(row[\""+name+"\"].ToString() != \"\"))");
                sb.AppendLine("             {");
                //int nvarchar ntext tinyint datetime
                                                if (type.Equals("int")) 
                                                {
                                                    sb.AppendLine("                 entity." + name + " = int.Parse(row[\"" + name + "\"].ToString());");
                                                }
                                                else if (type.Equals("tinyint"))
                                                {
                                                    sb.AppendLine("                 entity." + name + " = int.Parse(row[\"" + name + "\"].ToString());");
                                                }
                                                else if (type.Equals("datetime"))
                                                {
                                                    sb.AppendLine("                 entity." + name + " = DateTime.Parse(row[\"" + name + "\"].ToString());");
                                                }
                                                else if (type.Equals("nvarchar"))
                                                {
                                                    sb.AppendLine("                 entity." + name + " = String.Parse(row[\"" + name + "\"].ToString());");
                                                }
                                                else if (type.Equals("bit"))
                                                {
                                                    sb.AppendLine("                 entity." + name + " = byte.Parse(row[\"" + name + "\"].ToString());");
                                                }
                                                else
                                                {
                                                    sb.AppendLine("                 entity." + name + " = row[\"" + name + "\"].ToString();");
                                                }
                sb.AppendLine("             }");
            }

            return sb.ToString();
        }


        public static string BuildInsertField(List<ColumnEntity> columnList)
        {
            string str = "";
            for (int i = 0; i < columnList.Count;i++ )
            {
                if (columnList[i].Name.Equals("Id")) continue;
                str = str + " "+columnList[i].Name + ",";
            }
            return UtilsHelper.RemoveTheLastCharacte(str);
        }


        public static string BuildInsertParam(List<ColumnEntity> columnList)
        {
            string str = "";
            for (int i = 0; i < columnList.Count; i++)
            {
                if (columnList[i].Name.Equals("Id")) continue;
                str = str +"@"+ columnList[i].Name + ",";
            }
            return UtilsHelper.RemoveTheLastCharacte(str);
        }

        public static string BuildUpdateParam(List<ColumnEntity> columnList)
        {
            string str = "";
            for (int i = 0; i < columnList.Count; i++)
            {
                if (columnList[i].Name.Equals("Id")) continue;
                str = str +columnList[i].Name+"=@" + columnList[i].Name + ",";
            }
            return UtilsHelper.RemoveTheLastCharacte(str);
        }


        public static string BuildMySqlParameter(List<ColumnEntity> columnList,bool idCreateFlag) 
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("MySqlParameter[] parameters = {");
            for (int i = 0; i < columnList.Count; i++)
            {

                if (columnList[i].Name.Equals("Id")&&!idCreateFlag) continue;
                if (i == (columnList.Count - 1))
                {
                    sb.AppendLine("                 new MySqlParameter(\"@" + columnList[i].Name + "\",model." + columnList[i].Name + ")");
                }
                else
                {
                    sb.AppendLine("                 new MySqlParameter(\"@" + columnList[i].Name + "\",model." + columnList[i].Name + "),");
                }
            }
            sb.AppendLine("             };");
            return UtilsHelper.RemoveTheLastCharacte(sb.ToString());
        }



    }
}
